CREATE TABLE "DataSeriesCache" ("SeriesID" INTEGER NOT NULL ,"SiteID" INTEGER NOT NULL ,"VariableID" INTEGER NOT NULL ,"MethodID" INTEGER NOT NULL ,"SourceID" INTEGER NOT NULL ,"QualityControlLevelID" INTEGER NOT NULL ,"BeginDateTime" DATETIME NOT NULL ,"EndDateTime" DATETIME NOT NULL ,"BeginDateTimeUTC" DATETIME NOT NULL ,"EndDateTimeUTC" DATETIME NOT NULL ,"ValueCount" INTEGER,"ServiceID" INTEGER, PRIMARY KEY ("SeriesID") );
CREATE TABLE "DataServices" ("ServiceID" INTEGER PRIMARY KEY  NOT NULL ,"ServiceCode" nvarchar NOT NULL  DEFAULT '' ,"ServiceName" nvarchar NOT NULL  DEFAULT '' ,"ServiceType" nvarchar NOT NULL  DEFAULT '' ,"ServiceVersion" NUMERIC NOT NULL ,"ServiceProtocol" nvarchar NOT NULL  DEFAULT '' ,"ServiceEndpointURL" nvarchar NOT NULL  DEFAULT '' ,"ServiceDescriptionURL" nvarchar NOT NULL  DEFAULT '' ,"NorthLatitude" float,"SouthLatitude" float,"EastLongitude" float,"WestLongitude" float,"Abstract" nvarchar DEFAULT '' ,"ContactName" nvarchar DEFAULT '' ,"ContactEmail" nvarchar DEFAULT '' ,"Citation" nvarchar DEFAULT '' ,"IsHarvested" bit NOT NULL ,"HarveDateTime" DATETIME NOT NULL , "ServiceTitle" nvarchar);
CREATE TABLE "ISOMetadataCache" ("MetadataID" INTEGER PRIMARY KEY  NOT NULL ,"TopicCategory" nvarchar NOT NULL  DEFAULT 'Unknown' ,"Title" nvarchar NOT NULL  DEFAULT 'Unknown' ,"Abstract" nvarchar NOT NULL  DEFAULT 'Unknown' ,"ProfileVersion" nvarchar NOT NULL  DEFAULT 'Unknown' ,"MetadataLink" nvarchar);
CREATE TABLE "MethodsCache" ("MethodID" INTEGER PRIMARY KEY  NOT NULL ,"OriginMethodID" INTEGER NOT NULL ,"MethodDescription" nvarchar NOT NULL  DEFAULT '' ,"MethodLink" nvarchar NOT NULL  DEFAULT '' );
CREATE TABLE "QualityControlLevelsCache" ("QualityControlLevelID" INTEGER PRIMARY KEY  NOT NULL ,"OriginQualityControlLevelID" int NOT NULL  DEFAULT 0 ,"QualityControlLevelCode" nvarchar NOT NULL ,"Definition" nvarchar NOT NULL  DEFAULT '' ,"Explanation" nvarchar NOT NULL );
CREATE TABLE "SitesCache" ("SiteID" INTEGER PRIMARY KEY  NOT NULL ,"SiteCode" nvarchar,"SiteName" nvarchar,"Latitude" float NOT NULL ,"Longitude" float NOT NULL ,"LatLongDatumSRSID" INTEGER,"LatLongDatumName" nvarchar NOT NULL ,"Elevation_m" NUMERIC,"VerticalDatum" nvarchar,"LocalX" float,"LocalY" float,"LocalProjectionSRSID" INTEGER,"LocalProjectionName" nvarchar,"PosAccuracy_m" NUMERIC,"State" nvarchar,"County" nvarchar,"Comments" nvarchar);
CREATE TABLE "SourcesCache" ("SourceID" INTEGER PRIMARY KEY  NOT NULL ,"OriginSourceID" INTEGER NOT NULL  DEFAULT '' ,"Organization" nvarchar NOT NULL  DEFAULT '' ,"SourceDescription" nvarchar NOT NULL  DEFAULT '' ,"SourceLink" nvarchar NOT NULL  DEFAULT '' ,"ContactName" nvarchar NOT NULL  DEFAULT '' ,"Phone" nvarchar NOT NULL  DEFAULT 0 ,"Email" nvarchar NOT NULL  DEFAULT '' ,"Address" nvarchar NOT NULL  DEFAULT '' ,"City" nvarchar NOT NULL  DEFAULT '' ,"State" nvarchar NOT NULL  DEFAULT '' ,"ZipCode" NUMERIC NOT NULL  DEFAULT 0 ,"Citation" nvarchar NOT NULL  DEFAULT '' ,"MetadataID" INTEGER, "ServiceID" INTEGER);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE [sysdiagrams] (
	[name]		[nvarchar] NOT NULL,
	[principal_id]		[int] NOT NULL,
	[diagram_id]		[int] NOT NULL,
	[version]		[int] NULL,
	[definition]		[blob] NULL,
PRIMARY KEY ([diagram_id], [principal_id], [name])
);
CREATE TABLE "variablesCache" ("VariableID" INTEGER PRIMARY KEY  NOT NULL ,"VariableCode" nvarchar,"VariableName" nvarchar,"Speciation" nvarchar,"SampleMedium" nvarchar,"ValueType" nvarchar,"DataType" nvarchar,"GeneralCategory" nvarchar,"NoDataValue" NUMERIC,"VariableUnitsName" nvarchar,"VariableUnitsType" nvarchar,"VariableUnitsAbbreviation" TEXT,"IsRegular" bit,"TimeSupport" NUMERIC,"TimeUnitsName" nvarchar,"TimeUnitsType" nvarchar,"TimeUnitsAbbreviation" nvarchar);
CREATE INDEX "ix_QualityControl" ON "QualityControlLevelsCache" ("QualityControlLevelCode" ASC, "Definition" ASC);
CREATE INDEX "ix_dataSeries" ON "DataSeriesCache" ("SiteID" ASC, "VariableID" ASC, "MethodID" ASC, "SourceID" ASC, "QualityControlLevelID" ASC, "ServiceID" ASC);
CREATE UNIQUE INDEX "ix_dataSeriesId" ON "DataSeriesCache" ("SeriesID" ASC);
CREATE INDEX "ix_dataService" ON "DataServices" ("ServiceEndpointURL" ASC);
CREATE UNIQUE INDEX "ix_dataServices" ON "DataServices" ("ServiceID" ASC);
CREATE INDEX "ix_isoMd" ON "ISOMetadataCache" ("MetadataLink" ASC);
CREATE UNIQUE INDEX "ix_isoMdId" ON "ISOMetadataCache" ("MetadataID" ASC);
CREATE UNIQUE INDEX "ix_methodId" ON "MethodsCache" ("MethodID" ASC);
CREATE INDEX "ix_methods" ON "MethodsCache" ("MethodDescription" ASC, "MethodLink" ASC);
CREATE UNIQUE INDEX "ix_qualityControlId" ON "QualityControlLevelsCache" ("QualityControlLevelID" ASC);
CREATE INDEX "ix_sites" ON "SitesCache" ("SiteCode" ASC);
CREATE UNIQUE INDEX "ix_sitesId" ON "SitesCache" ("SiteID" ASC);
CREATE INDEX "ix_sources" ON "SourcesCache" ("Organization" ASC);
CREATE UNIQUE INDEX "ix_sourcesId" ON "SourcesCache" ("SourceID" ASC);
CREATE INDEX "ix_variables" ON "variablesCache" ("VariableCode" ASC);
CREATE UNIQUE INDEX "ix_variablesId" ON "variablesCache" ("VariableID" ASC);
CREATE UNIQUE INDEX [sysdiagrams_UK_principal_name]
ON [sysdiagrams]
([principal_id], [name]);
